Sub Query Mode

Overview

Sub query capability is provided within Microsoft MDX as a way to focus the main query on the relevant part of the model/cube. In many cases it can improve performance, especially when attempting to filter or slice a query.

Within BI Office, the Sub Query Mode switch turns ON/OFF the Sub Query Mode for the underlying MDX behind the query. In many cases, turning on Sub Query Mode can improve query response time.

Limitations and Issues

Before using Sub Query mode, you should be aware of the following issues.

No Calculations of Sets

BI Office sub queries cannot contain any calculations or sets. This severely limits them to queries with natural elements and basic selections only. Workarounds to this problem may exist, but can severely impact the breadth and depth of the calculation capabilities provided within BI Office.

Sub Queries vs. WHERE Clause

Sub queries often have a positive impact when multiple elements from a single hierarchy are chosen as the filter. When single elements from different dimensions are chosen as the filter, sub queries often perform no better than the standard WHERE clause.

Sub Query Selections Are Undone

Sub query filter selections can be undone by certain main query selections and functions (at times producing erroneous results).

Applying WHERE Twice

If context is required in the main query and it affects the slicers as well, then the context (WHERE) needs to be applied twice: once in the main query and once in the sub query. This can produce complex query statements and unintended results.

Note of Caution

It is important to bear in mind that sub queries in the Microsoft MDX engine have several drawbacks that make them impractical as a universal solution for all query types.

There are cases when sub-querying is not appropriate and can create errors - both in the MDX syntax and the result set. These errors are related to several core bugs in the Microsoft MDX sub query engine. As such, users are cautioned to use the Sub Query Mode only when needed and to thoroughly check the results.

Usage Tips

• The sub query mode should be left OFF unless specifically needed.
• When using Sub Query Mode, there is a wide variance in results and it's often difficult to determine whether the use of sub query mode is beneficial.
• The default setting of the Sub Query Mode is OFF. The user can change the default setting using the User Options.

Access Method

Open Query Options in Query Ribbon and select Sub Query Mode.

 

.